USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[Kyogi_Player]    Script Date: 05/09/2014 18:06:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Kyogi_Player]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Kyogi_Player]
GO

CREATE PROCEDURE [dbo].[Kyogi_Player]
	@KYOGINO int,
	@TAIKAINO int,
	@KYOGIMEI varchar(max)
AS
SELECT 
	AAA.KYOGISYANO, 
	AAA.NUMBER1 + convert(varchar(15), AAA.NUMBER2) AS NUMBER2, 
	AAA.KYOGISYAMEI,
	AAA.SEIBETU, 
	AAA.SEL_KYOGIMEI,
	AAA.SEL_KIROKU,
	MAX(CASE num WHEN 1 THEN AAA.KYOGIMEI ELSE '' END) AS S1,
	MAX(CASE num WHEN 2 THEN AAA.KYOGIMEI ELSE '' END) AS S2,
	MAX(CASE num WHEN 3 THEN AAA.KYOGIMEI ELSE '' END) AS S3,
	MAX(CASE num WHEN 4 THEN AAA.KYOGIMEI ELSE '' END) AS S4,
	MAX(CASE num WHEN 5 THEN AAA.KYOGIMEI ELSE '' END) AS S5
FROM 
	(SELECT 
		KS_SELK.KYOGISYANO, 
		KS_SELK.NUMBER1, 
		KS_SELK.NUMBER2, 
		KS_SELK.KYOGISYAKANA + '<br>' + KS_SELK.KYOGISYAMEI AS KYOGISYAMEI,
		dbo.Seibetu(KS_SELK.SEIBETU) AS SEIBETU, 
		KS_SELK.KYOGIMEI AS SEL_KYOGIMEI, 
		KS_SELK.KIROKU AS SEL_KIROKU, 
		KS_SELK.KIROKUKUBUN AS SEL_KIROKUKUBUN, 
		KS_SELK.KIROKUFLG AS SEL_KIROKUFLG, 
		(K.KYOGIMEI + '<br>' + (CASE WHEN SM.SYUMOKUKUBUN = 8 THEN LEFT(SK.KIROKU,(charindex('.', SK.KIROKU)-1)) ELSE(CASE WHEN SK.KIROKU IS NULL THEN '' ELSE 
		dbo.Kiroku(SK.KIROKU, SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,SK.KIROKUFLG) END)END)) AS KYOGIMEI,--2014/05/14
		SM.KIROKUKUBUN, 
		SK.KIROKUFLG,
		ROW_NUMBER() OVER(PARTITION BY KS_SELK.KYOGISYANO ORDER BY (CASE K.KYOGIMEI WHEN @KYOGIMEI THEN 1 ELSE 2 END), K.KYOGINO) AS num
	FROM 
		(SELECT 
			KS.TAIKAINO,
			KS.KYOGISYANO, 
			KS.NUMBER1, 
			KS.NUMBER2, 
			KS.KYOGISYAMEI,
			KS.KYOGISYAKANA,
			KS.SEIBETU,
			K.KYOGINO,
			K.KYOGIMEI, 
			SK.KIROKU, 
			SM.KIROKUKUBUN, 
			SK.KIROKUFLG 
		FROM 
			TBL_KYOGISYA AS KS, 
			TBL_SANKAKYOGI AS SK, 
			TBL_KYOGI AS K,
			TBL_SYUMOKU_MASTER AS SM 
		WHERE 
			KS.TAIKAINO = @TAIKAINO 
			AND (KS.KYOGISYAFLG = 0 OR KS.KYOGISYAFLG IS NULL) 
			AND KS.TAIKAINO = SK.TAIKAINO 
			AND KS.KYOGISYANO = SK.KYOGISYANO 
			AND SK.TAIKAINO = K.TAIKAINO 
			AND SK.KYOGINO = K.KYOGINO 
			AND K.SYUMOKUNO = SM.SYUMOKUNO 
			AND SK.KYOGINO = @KYOGINO ) AS KS_SELK 
		LEFT OUTER JOIN 
			TBL_SANKAKYOGI AS SK
		ON 
			KS_SELK.KYOGISYANO = SK.KYOGISYANO, 
			TBL_KYOGI AS K, 
			TBL_SYUMOKU_MASTER AS SM 
	WHERE 
		KS_SELK.TAIKAINO = SK.TAIKAINO 
		AND SK.TAIKAINO = K.TAIKAINO 
		AND SK.KYOGINO = K.KYOGINO 
		AND K.GATTAIFLG <> 2 
		AND K.SYUMOKUNO = SM.SYUMOKUNO) AS AAA
GROUP BY 
	AAA.KYOGISYANO, 
	AAA.NUMBER1, 
	AAA.NUMBER2, 
	AAA.KYOGISYAMEI,
	AAA.SEIBETU, 
	AAA.SEL_KYOGIMEI,
	AAA.SEL_KIROKU
ORDER BY 
	(CASE WHEN AAA.SEL_KIROKU IS NULL THEN 2 ELSE 1 END),
	AAA.SEL_KIROKU,
	AAA.KYOGISYANO
GO